package com.dataex.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.io.OutputFormat;
import org.dom4j.io.XMLWriter;

import com.dataex.Constants;
import com.dataex.util.DBConnectionRegister;

public class TableToXmlService {
	private static long limitCount = 0;

	public void exportDataToXMlFile(String dbName, String tableName) throws Exception {
		wirteXml(tableName, dbName);
	}

	// 定义静态函数访问数据库
	public static void accessDB(Document doc, Element root, String dbName, String tableName) {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			// 数据库连接字符串
			String url = "jdbc:mysql://192.168.19.128:3306/jdbca";
			// 连接数据库执行查询
			connection = DBConnectionRegister.getConnection();
			statement = connection.createStatement();
			// 获得数据库结果集
			String sql = null;
			limitCount = getLimitCount(tableName) <= 0 ? 1500 : getLimitCount(tableName);

			if (!Constants.limit) {
				if (getCount(tableName) <= getLimitCount(tableName) + 1500) {// 无分页
					return;
				}
				if ("ORACLE".equals(Constants.DBTYPE)) {
					sql = "select * from " + tableName;
				} else if ("MYSQL".equals(Constants.DBTYPE)) {
					if (getLimitCount(tableName) <= 0)
						sql = "select * from " + tableName + "LIMIT " + "1," + limitCount;
					else
						sql = "select * from " + tableName + "LIMIT " + getLimitCount(tableName) + "," + limitCount;
				}
			} else {
				sql = "select * from " + tableName;
			}
			System.out.println("@@@@sql@@@@" + sql);
			rs = statement.executeQuery(sql);
			// 生成xml文档
			createXml(getFileds(connection, tableName), doc, root, rs);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			DBConnectionRegister.close(connection, statement, rs);
		}
	}

	public static long getCount(String tableName) {
		Connection connection = null;
		Statement statement = null;
		ResultSet rs = null;
		try {
			connection = DBConnectionRegister.getConnection();
			statement = connection.createStatement();
			rs = statement.executeQuery("select count(0) as count from " + tableName);
			if (rs != null) {
				rs.next();
				return rs.getInt("count");
			}
			return 0;
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}

	}

	public static long getLimitCount(String tableName) throws Exception {
		File file = new File("d:/output/" + tableName + ".txt");
		InputStream intputStream = new FileInputStream(file);
		String count = null;
		byte[] buffer = new byte[(int) file.length()];
		intputStream.read(buffer);
		intputStream.close();
		String s = new String(buffer);
		if (s != null)
			return Long.valueOf(s);
		return 0;
	}

	public static void main(String[] args) {
		try {
			System.out.println(getLimitCount("gxpt_czrk_photo"));
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static List<String> getFileds(Connection conn, String tableName) {
		DatabaseMetaData dbmd = null;
		ResultSet rs = null;
		try {
			dbmd = conn.getMetaData();
			rs = dbmd.getColumns(null, "%", tableName, "%");
			List<String> list = new ArrayList<String>();
			while (rs.next()) {
				String columnName = rs.getString("COLUMN_NAME");
				list.add(columnName.toUpperCase());
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}

	/**
	 * 定义静态函数创建xml文档
	 * 
	 * @param filds
	 * @param doc
	 * @param root
	 * @param rs
	 * @throws SQLException
	 */
	public static void createXml(List filds, Document doc, Element root, ResultSet rs) throws SQLException {
		// 生成与表名对应的元素节点并添加到根元素节点下
		while (rs.next()) {
			Element row = root.addElement("Row");
			for (int i = 0; i < filds.size(); i++) {
				Element element = row.addElement((String) filds.get(i));
				if (rs.getString((String) filds.get(i)) != null) {
					element.setText(rs.getString((String) filds.get(i)));
				} else {
					element.setText("null");
				}
			}
		}
	}

	public static void wirteXml(String tableName, String dbName) {
		Element root = DocumentHelper.createElement("Rows");
		Document document = DocumentHelper.createDocument(root);
		// 访问数据库并将数据库信息封装进创建的xml文档中
		accessDB(document, root, dbName, tableName);
		// 指定文档输出格式
		OutputFormat format = new OutputFormat("   ", true);
		// 获取当前时间
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmm");

		String time = sf.format(new Date());

		// 把文档输出到存储设备，硬盘：第一种方式
		String fileName = tableName + "_" + time + ".xml";
		XMLWriter xmlWriter2 = null;
		try {
			xmlWriter2 = new XMLWriter(new FileOutputStream("xml/" + fileName), format);
			xmlWriter2.write(document);
			// 把文档输出到存储设备，硬盘：第二种方式
			XMLWriter xmlWriter3 = new XMLWriter(new FileWriter("xml/" + fileName), format);
			xmlWriter3.write(document);
			// 必须进行刷新和关闭，否则写入内容为空
			xmlWriter3.flush();
			File file = new File("d:/output/" + tableName + ".txt");
			if (file.exists()) {
				file.createNewFile();
			}
			FileOutputStream outputStream = new FileOutputStream(file);
			outputStream.write(String.valueOf(limitCount).getBytes());
			outputStream.flush();
			outputStream.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
